<?php
/**
 *
 */
class M_summary extends CI_Model {

  public function __construct() {
    parent::__construct();
    $this->load->database();
  }
  public function getSummarybyid($id){
    $sql = 'select * from summary where id='.$id;
    return $this->db->query($sql)->result_array();
  }
  public function getSummarybyid_t2($id){
    $sql = 'select * from summary_t2 where id='.$id;
    return $this->db->query($sql)->result_array();
  }
  public function getSummarybyid_t3($id){
    $sql = 'select * from summary_t3 where id='.$id;
    return $this->db->query($sql)->result_array();
  }
  public function getemSummary($emid,$bid,$maxday,$minday,$count,$offset){
    if(!empty($maxday) && !empty($minday)){
        $sql = 'select bname,addday,emname,shopname,content,inshop,outday,seeboss,getboss,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary,shops,brand,employees where summary.emid=employees.emid and shops.shopid=summary.shopid and brand.bid=summary.bid '.$emid.' '.$bid.' and addday>"'.$minday.'" and addday<"'.$maxday.'"';
      }else{
        $sql = 'select bname,addday,emname,shopname,content,inshop,outday,seeboss,getboss,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary,shops,brand,employees where summary.emid=employees.emid and shops.shopid=summary.shopid and brand.bid=summary.bid '.$emid.' '.$bid;
      }
      $sql .=' order by bname,emname,addday limit '.$offset.','.$count;
      return  array(
        'result' => $this->db->query($sql)->result_array(),
        'row' => $this->db->query($sql)->num_rows(),
        'sql' => $sql
      );
  }
  public function getemSummary_t2($emid,$bid,$maxday,$minday,$count,$offset){
    if(!empty($maxday) && !empty($minday)){
        $sql = 'select addday,emname,content,inshop,outday,seeboss,getboss,singledo,singlefuck,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary_t2,brand,employees where summary_t2.emid=employees.emid and brand.bid=summary_t2.bid '.$emid.' '.$bid.' and addday>"'.$minday.'" and addday<"'.$maxday.'"';
      }else{
        $sql = 'select bname,addday,emname,content,inshop,outday,seeboss,getboss,singledo,singlefuck,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary_t2,brand,employees where summary_t2.emid=employees.emid and brand.bid=summary_t2.bid '.$emid.' '.$bid;
      }
      $sql .=' order by bname,emname,addday limit '.$offset.','.$count;
      return  array(
        'result' => $this->db->query($sql)->result_array(),
        'row' => $this->db->query($sql)->num_rows(),
        'sql' => $sql
      );
  }
  public function getemSummary_t3($emid,$maxday,$minday,$count,$offset){
    if(!empty($maxday) && !empty($minday)){
          $sql = 'select addday,emname,zone,content,outday,runshop,meetcount,shopinfocount,bfshop,ysshop,ysgz,czshop,czyj,shopinfo,newboss from summary_t3,employees where summary_t3.emid=employees.emid '.$emid.' and addday>"'.$minday.'" and addday<"'.$maxday.'"';
      }else{
         $sql = 'select addday,emname,zone,content,outday,runshop,meetcount,shopinfocount,bfshop,ysshop,ysgz,czshop,czyj,shopinfo,newboss from summary_t3,employees where summary_t3.emid=employees.emid '.$emid;
      }
      $sql .=' order by emname,addday limit '.$offset.','.$count;
      return  array(
        'result' => $this->db->query($sql)->result_array(),
        'row' => $this->db->query($sql)->num_rows(),
        'sql' => $sql
      );
  }
  public function summarydelete_t1($id){
    $sql = 'delete from summary where id='.$id;
    return $this->db->simple_query($sql);
  }
  public function summarydelete_t2($id){
    $sql = 'delete from summary_t2 where id='.$id;
    return $this->db->simple_query($sql);
  }
  public function summarydelete_t3($id){
    $sql = 'delete from summary_t3 where id='.$id;
    return $this->db->simple_query($sql);
  }
  public function summaryupdate_t1($option){
    $sql = 'update summary set '.
           'shopid='.$option['shopid'].
           ',bid='.$option['bid'].
           ',content="'.$option['content'].'"'.
           ',getboss='.$option['getboss'].
           ',inshop='.$option['inshop'].
           ',joinv='.$option['joinv'].
           ',meet='.$option['meet'].
           ',outcount='.$option['outcount'].
           ',skcount='.$option['skcount'].
           ',outday='.$option['outday'].
           ',pass="'.$option['pass'].'"'.
           ',seeboss='.$option['seeboss'].
           ',sellcar='.$option['sellcar'].
           ',tecount='.$option['tecount'].
           ',emid='.$option['emid'].
           ' where id='.$option['id'];
    return $this->db->simple_query($sql);
  }
  public function summaryupdate_t2($option){
    $sql = 'update summary_t2 set '.
           'bid='.$option['bid'].
           ',content="'.$option['content'].'"'.
           ',getboss='.$option['getboss'].
           ',inshop='.$option['inshop'].
           ',joinv='.$option['joinv'].
           ',meet='.$option['meet'].
           ',outcount='.$option['outcount'].
           ',skcount='.$option['skcount'].
           ',outday='.$option['outday'].
           ',pass="'.$option['pass'].'"'.
           ',seeboss='.$option['seeboss'].
           ',sellcar='.$option['sellcar'].
           ',singledo='.$option['singledo'].
           ',singlefuck='.$option['singlefuck'].
           ',tecount='.$option['tecount'].
           ',emid='.$option['emid'].
           ' where id='.$option['id'];
    return $this->db->simple_query($sql);
  }
  public function summaryupdate_t3($option){
    $sql = 'update summary_t3 set '.
           'emid='.$option['emid'].
           ',content="'.$option['content'].'"'.
           ',zone='.$option['zone'].
           ',outday='.$option['outday'].
           ',runshop='.$option['runshop'].
           ',meetcount='.$option['meetcount'].
           ',shopinfocount='.$option['shopinfocount'].
           ',bfshop='.$option['bfshop'].
           ',ysshop='.$option['ysshop'].
           ',ysgz='.$option['ysgz'].
           ',czshop='.$option['czshop'].
           ',czyj='.$option['czyj'].
           ',shopinfo="'.$option['shopinfo'].'"'.
           ',newboss='.$option['newboss'].
           ',id='.$option['id'].
           ' where id='.$option['id'];
    return $this->db->simple_query($sql);
  }
  public function getemAllSummary($count,$offset){
    // if(!empty($maxday) && !empty($minday)){
    //   $sql = 'select bname,addday,emname,shopname,content,inshop,outday,seeboss,getboss,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary,shops,brand,employees where summary.emid=employees.emid and shops.shopid=summary.shopid and brand.bid=summary.bid and summary.emid='.$emid.' and addday>'.$minday.' and addday<'.$maxday;
    // }else{
    //   $sql = 'select bname,addday,emname,shopname,content,inshop,outday,seeboss,getboss,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary,shops,brand,employees where summary.emid=employees.emid and shops.shopid=summary.shopid and brand.bid=summary.bid and summary.emid='.$emid;
    // }
    $sql = 'select bname,addday,emname,shopname,content,inshop,outday,seeboss,getboss,tecount,skcount,outcount,joinv,sellcar,meet,pass from summary,shops,brand,employees where summary.emid=employees.emid and shops.shopid=summary.shopid and brand.bid=summary.bid';
    $sql .=' order by bname,emname,addday limit '.$offset.','.$count;
    return  array(
      'result' => $this->db->query($sql)->result_array(),
      'row' => $this->db->query($sql)->num_rows(),
    );
  }
  public function summaryadd($option){
    $sql='insert into summary(shopid,bid,content,getboss,inshop,joinv,meet,outcount,outday,pass,seeboss,sellcar,tecount,addday,emid,skcount)'.
    ' values("'.$option['shopid'].'","'.$option['bid'].'","'.$option['content'].'",'.$option['getboss'].','.$option['inshop'].','.$option['joinv'].','
    .$option['meet'].','.$option['outcount'].','.$option['outday'].',"'.$option['pass'].'",'.$option['seeboss'].','.$option['sellcar'].','.$option['tecount'].',"'.date("Y-m-d").'","'.$option['emid'].'",'.$option['skcount'].')';
    $this->db->simple_query($sql);
    if($option['shopid']!=='0'){
      $emname = $this->db->query('select emname from employees where emid='.$option['emid'])->result_array();
      $emname = $emname[0]['emname'];
      $val = date("Y.m.d").' '.$emname.':'.$option['content'];
      $sql2 = 'insert into shop_callback(shopid,shopcallback,addtime,author) values('.$option['shopid'].',"'.$val.'","'.date("Y-m-d H:i:s").'","'.$option['emid'].'")';
      $this->db->simple_query($sql2);
      return $sql;
    }
    return '1';
    // return $this->db->simple_query($sql);
  }
  public function summaryadd_t2($option){
    $sql = 'insert into summary_t2(emid,bid,content,inshop,outday,seeboss,getboss,tecount,skcount,outcount,singledo,singlefuck,joinv,sellcar,meet,pass,addday) '.
            'values('.$option['emid'].','.$option['bid'].',"'.$option['content'].'",'.$option['inshop'].','.$option['outday'].','.$option['seeboss'].','.$option['getboss'].','.$option['tecount'].
            ','.$option['skcount'].','.$option['outcount'].','.$option['singledo'].','.$option['singlefuck'].','.$option['joinv'].','.$option['sellcar'].','.$option['meet'].',"'.$option['pass'].'",'.
            '"'.date("Y-m-d H:i:s").'")';
    return $this->db->simple_query($sql);
  }
  public function summaryadd_t3($option){
    $sql = 'insert into summary_t3(emid,zone,content,outday,runshop,meetcount,shopinfocount,bfshop,ysshop,ysgz,czshop,czyj,shopinfo,newboss,addday) '.
    'values('.$option['emid'].',"'.$option['zone'].'","'.$option['content'].'",'.$option['outday'].','.$option['runshop'].','.$option['meetcount'].','.$option['shopinfocount'].','.$option['bfshop'].','.$option['ysshop'].
    ','.$option['ysgz'].','.$option['czshop'].','.$option['czyj'].',"'.$option['shopinfo'].'",'.$option['newboss'].',"'.date("Y-m-d H:i:s").'")';
    return $this->db->simple_query($sql);
  }
  public function gettecountbrand($bid,$month,$year,$count,$offset){
    if($month!=''&&$year!=''){
      $sql='select *,shopname from summary,shops where summary.shopid=shops.shopid and bid='.$bid.' and MONTH(addday)='.$month.' and YEAR(addday)='.$year.' order by addday limit '.$offset.','.$count;
    }else{
      $sql='select *,shopname from summary,shops where summary.shopid=shops.shopid and bid='.$bid.' order by addday limit '.$offset.','.$count;
    }
    return array(
      'result'  => $this->db->query($sql)->result_array(),
      'row'     => $this->db->query($sql)->num_rows()
    );
    // return $this->db->query($sql)->result_array();
  }
  public function gettebrand($bid){
    $sql='select terminal.teid,tename,shopname from tebrand,terminal,teshop,shops where terminal.teid=teshop.teid and shops.shopid=teshop.shopid and terminal.teid=tebrand.teid and tebrand.bid='.$bid.' order by terminal.teid';
    return $this->db->query($sql)->result_array();
  }
  public function getMySummarylist($emid,$count,$offset){
  $sql = 'select * from (select * from summary where emid='.$emid.' order by id desc limit '.$count.') summary,shops,brand where shops.shopid=summary.shopid and summary.bid = brand.bid';
    $total = $this->db->query('select count(id) from summary where emid='.$emid)->result_array();
    $query = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'total'    => $total[0]['count(id)'],
      'sql' => $sql
    );
  }
  public function getSummarylist($count,$offset){
    $sql = 'select summary.*,shopname,emname,bname from summary,shops,brand,employees where employees.emid=summary.emid and summary.shopid=shops.shopid and brand.bid=summary.bid'.
           ' UNION ALL SELECT summary.*,shopid AS "shopname",bname,emname FROM summary, brand, employees WHERE employees.emid = summary.emid AND brand.bid = summary.bid and shopid = 0 order by id desc limit '.
           $offset.','.$count;
    $total = $this->db->query('select id from summary');
    $query = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'total'    => $total->num_rows()
    );
  }
  public function getSummarySelect($key,$count,$offset){
    $key = '"%'.$key.'%"';
    $sql = 'select * from summary,shops,brand,employees where employees.emid=summary.emid and summary.shopid=shops.shopid and brand.bid=summary.bid '.
           'and ( '.
           'bname like '.$key.' or '.
           'shopname like '.$key.' or '.
           'emname like '.$key.' or '.
           'content like '.$key.
           ' ) order by addday,id desc';
    $query = $this->db->query($sql.' limit '.$offset.','.$count);
    $total = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'sql' => $sql,
      'total'    => $total->num_rows(),
      'totallist'=> $total->result_array()
    );
  }
  public function getSummarylist_t2($count,$offset){
    $sql = 'select summary_t2.*, emname, bname FROM summary_t2, employees, brand WHERE summary_t2.bid=brand.bid AND summary_t2.emid=employees.emid ORDER BY addday,id desc'.
           ' limit '.$offset.','.$count;
    $total = $this->db->query('select * from summary_t2');
    $query = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'total'    => $total->num_rows(),
      'totallist'=> $total->result_array()
    );
  }
  public function getSummarySelect_t2($key,$count,$offset){
    $key = '"%'.$key.'%"';
    $sql = 'select summary_t2.*, emname, bname FROM summary_t2, employees, brand WHERE summary_t2.bid=brand.bid AND summary_t2.emid=employees.emid '.
           'and ( '.
           'bname like '.$key.' or '.
           'emname like '.$key.' or '.
           'content like '.$key.
           ' ) order by addday,id desc';
    $query = $this->db->query($sql.' limit '.$offset.','.$count);
    $total = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'sql' => $sql,
      'total'    => $total->num_rows(),
      'totallist'=> $total->result_array()
    );
  }
  public function getSummarylist_t3($count,$offset){
    $sql = 'select summary_t3.*, emname FROM summary_t3, employees WHERE summary_t3.emid=employees.emid ORDER BY addday,id desc limit '.
           $offset.','.$count;
    $total = $this->db->query('select * from summary_t3');
    $query = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'total'    => $total->num_rows(),
      'totallist'=> $total->result_array()
    );
  }
  public function getSummarySelect_t3($key,$count,$offset){
    $key = '"%'.$key.'%"';
    $sql = 'select summary_t3.*, emname FROM summary_t3, employees WHERE summary_t3.emid=employees.emid '.
           'and ( '.
           'emname like '.$key.' or '.
           'content like '.$key.
           ' ) order by addday,id desc';
    $query = $this->db->query($sql.' limit '.$offset.','.$count);
    $total = $this->db->query($sql);
    return array(
      'summarylist' => $query->result_array(),
      'sql' => $sql,
      'total'    => $total->num_rows(),
      'totallist'=> $total->result_array()
    );
  }
}
